using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Windows;
using WpfApp1.Models;

namespace WpfApp1.DataAccess;

public class StudentIsDataAccess : IStuDataAccess
{
    private readonly MySqlConnection _connection = SqlAccess.CreateConnection();

    //获取所有学生信息
    public List<Student> GetStudents()
    {
        List<Student> students = new List<Student>();
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand("SELECT * FROM students", connection);
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    students.Add(new Student(
                        reader.GetInt32("student_id"), 
                        reader.GetString("name"),
                        reader.GetString("student_number"), 
                        reader.GetInt32("gender"),
                        reader.GetInt32("age"), 
                        reader.GetString("department")
                    ));
                }
            }
        }
        return students;
    }

    public Student? GetStudentByNumber(string studentNumber)
    {
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = "SELECT * FROM students WHERE student_number = @student_number";
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@student_number", studentNumber);
            using (var reader = command.ExecuteReader())
            {
                if (!reader.Read()) return null;
                return new Student(
                    reader.GetInt32("student_id"), 
                    reader.GetString("name"),
                    reader.GetString("student_number"), 
                    reader.GetInt32("gender"),
                    reader.GetInt32("age"), 
                    reader.GetString("department")
                );
            }
        }
    }

    //修改学生信息(性别，年龄，所在系)
    public void UpdateStudent(Student student)
    {
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = "UPDATE students SET gender=@gender, age=@age, department=@department WHERE student_number=@student_number";
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@gender", student.Gender);
            command.Parameters.AddWithValue("@age", student.Age);
            command.Parameters.AddWithValue("@department", student.Department);
            command.Parameters.AddWithValue("@student_number", student.StudentNumber);
            var row = command.ExecuteNonQuery();
            MessageBox.Show(row == 1 ? "修改成功！" : "修改失败！");
        }
    }   

    //添加学生信息
    public void AddStudent(Student student)
    {
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            
            // 先检查学号是否已存在
            var checkSql = "SELECT COUNT(*) FROM students WHERE student_number = @student_number";
            var checkCommand = new MySqlCommand(checkSql, connection);
            checkCommand.Parameters.AddWithValue("@student_number", student.StudentNumber);
            var count = Convert.ToInt32(checkCommand.ExecuteScalar());
            
            if (count > 0)
            {
                MessageBox.Show("该学号已存在，请检查后重试！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }

            var sql = "INSERT INTO students (student_number, name, gender, age, department) VALUES (@student_number, @name, @gender, @age, @department)";
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@student_number", student.StudentNumber);
            command.Parameters.AddWithValue("@name", student.Name);
            command.Parameters.AddWithValue("@gender", student.Gender);
            command.Parameters.AddWithValue("@age", student.Age);
            command.Parameters.AddWithValue("@department", student.Department);
            var row = command.ExecuteNonQuery();
            MessageBox.Show(row == 1 ? "添加成功！" : "添加失败！");
        }
    }

    public void DeleteStudentByNumber(Student student)
    {
        using (var connection = SqlAccess.CreateConnection())
        {
            connection.Open();
            var sql = "DELETE FROM students WHERE student_number = @student_number";
            var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@student_number", student.StudentNumber);
            var row = command.ExecuteNonQuery();
            MessageBox.Show(row == 1 ? "删除成功！" : "删除失败！");
        }
    }
}